package com.example.demo.account.repository;

import com.example.demo.account.entity.SysUser;
import com.example.demo.account.entity.SysUserRole;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author littlecar
 * @date 2020/3/16 14:25
 */
@Repository
public interface UserRoleRepository extends JpaRepository<SysUserRole,Long>{
    @Query(value = "from  SysUserRole s where s.user_id =?1 order by s.id")
    List<SysUserRole> findByUser_id(Long id);

    @Query(value="delete from SysUserRole s where s.user_id=?1 and s.role_id=?2 ")
    void deteleByUserIdAndRoleId(Long user_id, Long role_id);

    @Query(value = "from  SysUserRole s where s.user_id =?1 and s.role_id=?2 order by s.id")
    List<SysUserRole> findByUserIdAndRoleId(Long userId, Long roleId);

    @Query(value = "from  SysUserRole s where  s.role_id=4 order by s.id")
    List<SysUserRole> findAllSchoolAdmin();

    @Modifying
    @Query(value = "update SysUserRole s set s.school_id=?2 where  s.role_id=?3 and s.user_id=?1 ")
    void updateSchoolId(Long user_id, Long school_id, Long role_id);

    @Query(value = "select s.user_id from  SysUserRole s where s.school_id=?1 and s.role_id=?2 ")
    List<Long> findUserIdBySchoolIdAndRoleId(Long school_id, Long role_id);

    @Query( nativeQuery=true, value = "select s.* from user_role s where s.user_id=?1 and s.role_id=?2 and s.school_id is null limit 1")
    SysUserRole findOneByUserIdAndRoleId(Long user_id, Long role_id);

    @Query( nativeQuery=true, value = "select s.* from user_role s where s.user_id=?1 and s.role_id=?2 and s.school_id=?3 ")
    SysUserRole findByUserIdAndRoleIdAndSchoolId(Long user_id, Long role_id, Long school_id);

    @Modifying
    @Query( nativeQuery=true, value = "delete s.* from user_role s where s.user_id=?1 and s.role_id=?2 and s.school_id=?3 ")
    void deleteByUserIdAndRoleIdAndSchoolId(Long user_id, Long role_id, Long school_id);

    @Modifying
    @Query( nativeQuery=true, value = "delete s.* from user_role s where s.school_id=?1 ")
    void deleteBySchoolId(Long school_id);
}
